
USE [master]
GO

/****** Object:  Database [DCHealthCheck] ******/
CREATE DATABASE [DCHealthCheck] ON  PRIMARY 
( NAME = N'DCHealthCheck', FILENAME = N'E:\MSSQL\DATA\DCHealthCheck.mdf' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )
 LOG ON 
( NAME = N'DCHealthCheck_log', FILENAME = N'E:\MSSQL\DATA\DCHealthCheck_log.ldf' , SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [DCHealthCheck] SET COMPATIBILITY_LEVEL = 100
GO

ALTER DATABASE [DCHealthCheck] SET RECOVERY SIMPLE
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DCHealthCheck].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [DCHealthCheck] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [DCHealthCheck] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [DCHealthCheck] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [DCHealthCheck] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [DCHealthCheck] SET ARITHABORT OFF 
GO

ALTER DATABASE [DCHealthCheck] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [DCHealthCheck] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [DCHealthCheck] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [DCHealthCheck] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [DCHealthCheck] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [DCHealthCheck] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [DCHealthCheck] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [DCHealthCheck] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [DCHealthCheck] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [DCHealthCheck] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [DCHealthCheck] SET  DISABLE_BROKER 
GO

ALTER DATABASE [DCHealthCheck] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [DCHealthCheck] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [DCHealthCheck] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [DCHealthCheck] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [DCHealthCheck] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [DCHealthCheck] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [DCHealthCheck] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [DCHealthCheck] SET  READ_WRITE 
GO

ALTER DATABASE [DCHealthCheck] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [DCHealthCheck] SET  MULTI_USER 
GO

ALTER DATABASE [DCHealthCheck] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [DCHealthCheck] SET DB_CHAINING OFF 
GO

--
USE [DCHealthCheck]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

/**  Table [dbo].[DomainController] **/

CREATE TABLE [dbo].[DomainController](
 [Id] [uniqueidentifier] NOT NULL, -- -- the objectGuid of the DC computer account
 [clusterId] [bigint] IDENTITY(1,1) NOT NULL,
 [DN] [varchar] (900) NOT NULL, -- the distinguishedName of the DC computer account
 [SamAccountName] [varchar] (20) NOT NULL,
 [DNSName] [varchar](255) NOT NULL, -- DNS Name (FQDN) (DNSHostName attribute)
 [DNSDomain] [varchar](255) NOT NULL, 
 [NetbiosDomain] [varchar](15) NOT NULL, 
 [ADSite] [varchar](255) NOT NULL, -- DomainController.SiteName property
 [OSName] [varchar](255) NOT NULL, 
 [OSVersion] [varchar] (255) NOT NULL,
 [WhenCreated] [datetime2] NOT NULL, -- computer account whenCreated
 [RowVersion] [timestamp] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[DomainController] ADD CONSTRAINT [PK_DomainController] PRIMARY KEY NONCLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DomainController]
ADD CONSTRAINT IX_DomainController_UniqueClusterId UNIQUE CLUSTERED (clusterId)
GO

/* DNSName is non-unique due to the DC may be deleted and re-created during an OS upgrade.  The objectGUID would change, but the other information may remain the same.*/
CREATE NONCLUSTERED INDEX [IX_DomainController_DNSName] ON [dbo].[DomainController] 
(
 [DNSName] ASC
)
INCLUDE ([ADSite], [DNSDomain]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/* DN is non-unique due to the DC may be deleted and re-created during an OS upgrade.  The objectGUID would change, but the other information may remain the same.*/
CREATE NONCLUSTERED INDEX [IX_DomainController_DN] ON [dbo].[DomainController] 
(
 [DN] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[HealthCheckSummary] **/

CREATE TABLE [dbo].[HealthCheckSummary](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [CheckDateTimeUTC] [datetime2] NOT NULL, 
 [CheckDateUTC]  AS (CONVERT([date],[CheckDateTimeUTC],(0))),
 [Summary] [varchar] (max) NOT NULL,
 [DCsTested] [int] NOT NULL,
 [DCsWithErrors] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[HealthCheckSummary] ADD CONSTRAINT [PK_HealthCheckSummary] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[SystemInfo] **/

CREATE TABLE [dbo].[SystemInfo](
 [Id] [uniqueidentifier] NOT NULL,
 [clusterId] [bigint] IDENTITY(1,1) NOT NULL,
 --[CheckDateUTC] [date] NOT NULL,
 --[CheckTimeUTC] [time] NOT NULL,
 --[CheckDateTimeUTC] AS (DATEADD(DAY, DATEDIFF(DAY, '1900-01-01', [CheckDateUTC]), CAST([CheckTimeUTC] AS DATETIME2(7)))), -- this is not deterministic and cannot be persisted
 [CheckDateTimeUTC] [datetime2] NOT NULL, -- time that the application began the check.  This will be the same for each server during a check. 
 [CheckDateUTC] AS CAST(CheckDateTimeUTC AS DATE) PERSISTED NOT NULL, 
 --
 [IPAddress] [varchar](255) NOT NULL, -- resolve the DNSHostName to an ip address
 [IPAddresses] [varchar](255) NOT NULL, -- all IP addresses for all network adapters from Win32_NetworkAdapterConfiguration WHERE IPEnabled=true
 --
 [Manufacturer] [varchar] (255) NOT NULL, -- Win32_ComputerSystem\Manufacturer
 [Model] [varchar] (255) NOT NULL, -- Win32_ComputerSystem\Model
 [SerialNumber] [varchar] (255) NOT NULL, -- Win32_ComputerSystemProduct\IdentifyingNumber
 [CPUCount] [int] NOT NULL, -- Win32_ComputerSystem\NumberOfProcessors
 [LogicalCPUCount] [int] NOT NULL, -- Win32_ComputerSystem\NumberOfLogicalProcessors
 --
 [UTCOffset] [int] NOT NULL, -- Win32_OperatingSystem\CurrentTimeZone (minutes offset from UTC)
 [DST] [bit] NOT NULL, -- Win32_ComputerSystem\DaylightInEffect (True/False)
 [StartupDateTimeUTC] [datetime2] NOT NULL, -- Win32_OperatingSystem\LastBootUpTime
 [UptimeDays] [int] NOT NULL, -- subtract StartupTimeUTC from current DateTimeUTC
 [DCTime] [datetime2] NOT NULL, -- Win32_OperatingSystem\LocalDateTime
 [DCTimeUTC] [datetime2] NOT NULL, -- calculated using DCLocalTime + UTCOffset
 [TimeDiff] [varchar] (255) NOT NULL, -- TimeSpan.ToString()
 [OSInstallDate] [datetime2] NOT NULL, -- Win32_OperatingSystem\InstallDate 
 --
 [ProcessCount] [int] NOT NULL, -- Win32_OperatingSystem\NumberOfProcesses
 [FreeMemoryMB] [int] NOT NULL, -- Win32_OperatingSystem\FreePhysicalMemory
 [TotalMemoryMB] [int] NOT NULL, -- Win32_OperatingSystem\TotalVisibleMemorySize 
 [TotalHandles] [int] NOT NULL, -- summed From ProcessInfo list
 [TotalThreads] [int] NOT NULL, -- summed From ProcessInfo list
 [CDriveTotalMB] [int] NOT NULL, -- Win32_LogicalDisk\Size
 [CDriveFreeMB] [int] NOT NULL, -- Win32_LogicalDisk\FreeSpace
 [TcpConnections] [int] NOT NULL, --Win32_PerfFormattedData_Tcpip_TCPv4\ConnectionsEstablished
 [TotalLogons] [int] NOT NULL, -- Win32_PerfFormattedData_PerfNet_Server\LogonTotal
 --
 [DNSResponse] [varchar] (255) NOT NULL, -- query the DNS server for it's own A record
 [DNSListenAddresses] [varchar] (255) NOT NULL, -- Get the registry value: HKLM\System\CurrentControlSet\Services\DNS\Parameters\ListenAddresses
 [DNSPublishAddresses] [varchar] (255) NOT NULL, -- Get the registry value: HKLM\System\CurrentControlSet\Services\DNS\Parameters\PublishAddresses
 [DNSScavengingInterval] [int] NOT NULL, -- HKLM\System\CurrentControlSet\Services\DNS\Parameters\ScavengingInterval 
 [DNSQueryReceived] [bigint] NOT NULL, -- Win32_PerfFormattedData_DNS_DNS\TotalQueryReceived
 [DNSResponseSent] [bigint] NOT NULL, --Win32_PerfFormattedData_DNS_DNS\TotalResponseSent
 [DNSRecursiveQueries] [bigint] NOT NULL,-- Win32_PerfFormattedData_DNS_DNS\RecursiveQueries
 --
 [IsGlobalCatalog] [bit] NOT NULL,  
 [IsPDCEmulator] [bit] NOT NULL, 
 [IsInfrastructureManager] [bit] NOT NULL, 
 [IsRIDManager] [bit] NOT NULL, 
 [IsISTG] [bit] NOT NULL, 
 [DCFlags] [int] NOT NULL, -- Flags output from NLTEST: PDC | GC | DS | LDAP | KDC | TimeServ | Writable | DNSDC | DNSDomain | DNSForest | CloseSite | FullSecret | WS
 [RepInMB] [int] NOT NULL, -- Win32_PerfFormattedData_NTDS_NTDS\DRAInboundBytesTotalSinceBoot (may rollover?)
 [RepOutMB] [int] NOT NULL, -- Win32_PerfFormattedData_NTDS_NTDS\DRAOutboundBytesTotalSinceBoot (may rollover?)
 [HighestCommittedUSN] [bigint] NOT NULL, -- DomainController.HighestCommittedUsn
 [PendingReplicationCount] [int] NOT NULL, -- DomainController.GetReplicationOperationInformation().PendingOperations.Count
 [NTDSDitSizeMB] [int] NOT NULL,
 [NeighborCount] [int] NOT NULL, -- DomainController.GetAllReplicationNeighbors().Count()
 [SysvolReplicationType] [int] NOT NULL, 
 [SysvolMigrationState] [int] NOT NULL, 
 [GpRsopHtml] [varbinary] (max), -- compressed byte array
 [GpRsopXml] [varbinary] (max), -- compressed byte array  
 --
 [PingTest] [int] NOT NULL, -- 0 = NotPerformed, 1 = Success, 2 = Fail
 [PingRttMs] [int] NOT NULL, -- Round Trip Time in milliseconds
 [PingLossPct] [int] NOT NULL, -- Ping loss percentage
 [LdapBindTest] [int] NOT NULL, -- 0 = NotPerformed, 1 = Success, 2 = Fail
 [LdapSSLBindTest] [int] NOT NULL, -- 0 = NotPerformed, 1 = Success, 2 = Fail
 [GCBindTest] [int] NOT NULL, -- 0 = NotPerformed, 1 = Success, 2 = Fail
 [GCSSLBindTest] [int] NOT NULL, -- 0 = NotPerformed, 1 = Success, 2 = Fail
 [DNSTest] [int] NOT NULL, -- 0 = NotPerformed, 1 = Success, 2 = Fail (Note: DNS queries attempt UDP before TCP.)
 [SysvolTest] [int] NOT NULL, -- 0 = NotPerformed, 1 = Success, 2 = Fail
 [TimeServerTest] [int] NOT NULL, -- 0 = NotPerformed, 1 = Success, 2 = Fail.  Performs a test to NTP port UDP/123.
 [TimeSyncTest] [int] NOT NULL, -- 0 = NotPerformed, 1 = Success, 2 = Fail.  Measures the difference between the server performing the test and the domain controller being tested to determine if it exceeds a threshold.
 [StrictReplicationTest] [int] NOT NULL, -- 0 = NotPerformed, 1 = Success, 2 = Fail. Checks for registry value: 'Strict Replication Consistency'  Key: HKLM\SYSTEM\CurrentControlSet\Services\NTDS\Parameters\ 
 --
 [ErrorMessage] [varchar](max) NULL,
 [HealthCheckSummaryId] [int] NOT NULL, -- FK to HealthCheckSummary table
 [domainControllerId] [uniqueidentifier] NOT NULL, -- Foreign Key to the status check record for the DC record
 [RowVersion] [timestamp] NOT NULL 
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SystemInfo] ADD CONSTRAINT [PK_SystemInfo] PRIMARY KEY NONCLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SystemInfo]
ADD CONSTRAINT IX_SystemInfo_UniqueClusterId UNIQUE CLUSTERED (clusterId)
GO

CREATE NONCLUSTERED INDEX [IX_SystemInfo_CheckDateTimeUTC] ON [dbo].[SystemInfo] 
(
	[CheckDateUTC] ASC,
	[CheckDateTimeUTC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_SystemInfo_DomainControllerId] ON [dbo].[SystemInfo] 
(
 [domainControllerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[Certificate] **/

CREATE TABLE [dbo].[Certificate](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[X509CertificateRawData] [varbinary](max) NOT NULL,
	[FriendlyName] [varchar](1024) NOT NULL,
	[EffectiveDate] [datetime2](7) NOT NULL,
	[ExpirationDate] [datetime2](7) NOT NULL,
	[IssuerName] [varchar](1024) NOT NULL,
	[PublicKey] [varchar](max) NOT NULL,
	[SerialNumber] [varchar](1024) NOT NULL,
	[SubjectName] [varchar](1024) NOT NULL,
	[Thumbprint] [varchar](1024) NOT NULL,
	[systemInfoId] [uniqueidentifier] NOT NULL -- Foreign Key to the SystemInfo record 
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Certificate] ADD CONSTRAINT [PK_Certificate] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Certificate_SystemInfoId] ON [dbo].[Certificate] 
(
 [systemInfoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[ProcessInfo] **/

CREATE TABLE [dbo].[ProcessInfo](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [ProcessId] [bigint] NOT NULL,
 [ParentProcessId] [bigint] NOT NULL,
 [Priority] [int] NOT NULL,
 [StartDateTime] [datetime2] NOT NULL,
 [ElapsedTimeTicks] [bigint] NOT NULL, -- TimeSpan.Ticks
 [ElapsedTime] [varchar] (255) NOT NULL, -- TimeSpan.ToString()
 [KernelModeTicks] [bigint] NOT NULL,
 [UserModeTicks] [bigint] NOT NULL,
 [CPUTicks]  AS ([KernelModeTicks]+[UserModeTicks]), -- computed 
 [CPUTime] [varchar] (255) NOT NULL, -- TimeSpan.ToString()
 [CommandLine] [varchar] (2048) NOT NULL,
 [ExecutablePath] [varchar] (2048) NOT NULL,
 [Executable] [varchar] (900) NOT NULL, -- file name only from ExecutablePath
 [PageFaults] [bigint] NOT NULL,
 [VirtualMemoryBytes] [bigint] NOT NULL,
 [WorkingSetBytes] [bigint] NOT NULL,
 [PrivateBytes] [bigint] NOT NULL,
 [PagePoolBytes] [bigint] NOT NULL,
 [NonPagePoolBytes] [bigint] NOT NULL,
 [HandleCount] [int] NOT NULL,
 [ThreadCount] [bigint] NOT NULL,
 [ReadBytes] [bigint] NOT NULL,
 [WriteBytes] [bigint] NOT NULL,
 [OtherBytes] [bigint] NOT NULL,
 [systemInfoId] [uniqueidentifier] NOT NULL -- Foreign Key to the SystemInfo record 
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ProcessInfo] ADD CONSTRAINT [PK_ProcessInfo] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ProcessInfo_SystemInfoId] ON [dbo].[ProcessInfo] 
(
 [systemInfoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[RoleFeature] **/

CREATE TABLE [dbo].[RoleFeature](
 [Id] [bigint] IDENTITY(1001,1) NOT NULL,
 [FeatureId] [int] NOT NULL, 
 [Name] [varchar] (255) NOT NULL, 
 [ParentFeatureId] [int] NOT NULL,
 [systemInfoId] [uniqueidentifier] NOT NULL -- Foreign Key to the SystemInfo record 
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleFeature] ADD CONSTRAINT [PK_RoleFeature] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


 CREATE NONCLUSTERED INDEX [IX_RoleFeature_SystemInfoId] ON [dbo].[RoleFeature] 
(
 [systemInfoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[ServiceInfo] **/

CREATE TABLE [dbo].[ServiceInfo](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [DisplayName] [varchar] (255) NOT NULL, 
 [ErrorControl] [int] NOT NULL, 
 [ExitCode] [bigint] NOT NULL, 
 [Name] [varchar] (255) NOT NULL, 
 [PathName] [varchar] (255) NOT NULL, 
 [ProcessId] [bigint] NOT NULL,
 [ServiceType] [int] NOT NULL,  
 [Started] [bit] NOT NULL,  
 [StartMode] [int] NOT NULL,  
 [StartName] [varchar] (255) NOT NULL,  
 [State] [int] NOT NULL,
 [Status] [int] NOT NULL,
 [systemInfoId] [uniqueidentifier] NOT NULL -- Foreign Key to the SystemInfo record 
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ServiceInfo] ADD CONSTRAINT [PK_ServiceInfo] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ServiceInfo_SystemInfoId] ON [dbo].[ServiceInfo] 
(
 [systemInfoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ServiceInfo_Name] ON [dbo].[ServiceInfo] 
(
	[DisplayName] ASC,
	[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ServiceInfo_PathName] ON [dbo].[ServiceInfo] 
(
	[PathName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[InstalledProduct] **/

CREATE TABLE [dbo].[InstalledProduct](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [Description] [varchar] (900) NOT NULL,
 [Name] [varchar] (900) NOT NULL,
 [IdentifyingNumber] [uniqueidentifier] NOT NULL,
 [InstallDate] [date] NOT NULL,
 [Vendor] [varchar] (900) NOT NULL,
 [Version] [varchar] (255) NOT NULL,
 [systemInfoId] [uniqueidentifier] NOT NULL -- Foreign Key to the SystemInfo record
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[InstalledProduct] ADD CONSTRAINT [PK_InstalledProduct] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_InstalledProduct_SystemInfoId] ON [dbo].[InstalledProduct] 
(
 [systemInfoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


/**  Table [dbo].[ReplicationNeighbor] **/

CREATE TABLE [dbo].[ReplicationNeighbor](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [SourceServer] [varchar](255) NOT NULL,
 [SourceInvocationId] [uniqueIdentifier] NOT NULL,
 [ConsecutiveFailureCount] [int] NOT NULL,
 [LastAttemptedSyncUTC] [datetime2] NOT NULL, 
 [LastSuccessfulSyncUTC] [datetime2] NOT NULL, 
 [LastSyncMessage] [varchar](max) NOT NULL,
 [LastSyncResult] [int] NOT NULL,
 [PartitionName] [varchar] (1024) NOT NULL,
 [ReplicationNeighborOption] [varchar](900) NOT NULL,
 [ReplicationReason] [varchar](900) NOT NULL,
 [USNAttributeFilter] [bigint] NOT NULL,
 [USNLastObjectChangeSynced] [bigint] NOT NULL,
 [ErrorMessage] [varchar](max) NULL, 
 [systemInfoId] [uniqueidentifier] NOT NULL -- Foreign Key to the status check record for the DC that has the connection
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ReplicationNeighbor] ADD CONSTRAINT [PK_ReplicationNeighbor] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ReplicationNeighbor_ReplicationReason] ON [dbo].[ReplicationNeighbor] 
(
 [ReplicationReason] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ReplicationNeighbor_SystemInfoId] ON [dbo].[ReplicationNeighbor] 
(
 [systemInfoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[PendingReplication] **/

CREATE TABLE [dbo].[PendingReplication](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [OperationNumber] [int] NOT NULL,
 [OperationType] [varchar] (1024) NOT NULL,
 [PartitionName] [varchar] (1024) NOT NULL,
 [Priority] [int] NOT NULL, 
 [SourceServer] [varchar] (255) NOT NULL, 
 [TimeEnqueued] [datetime2] NOT NULL,
 [systemInfoId] [uniqueidentifier] NOT NULL -- Foreign Key to the status check record for the DC that has the connection
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PendingReplication] ADD CONSTRAINT [PK_PendingReplication] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_PendingReplication_SystemInfoId] ON [dbo].[PendingReplication] 
(
 [systemInfoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[TagObject] **/

CREATE TABLE [dbo].[TagObject](
 [DN] [varchar] (900) NOT NULL,
 [DNSDomain] [varchar] (255) NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TagObject] ADD CONSTRAINT [PK_TagObject] PRIMARY KEY CLUSTERED 
(
 [DN] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[TagObjectValues] **/

CREATE TABLE [dbo].[TagObjectValues](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [TagValue] [datetime2] NOT NULL,
 [systemInfoId] [uniqueidentifier] NOT NULL, -- Foreign Key to the status check record for the DC that has the connection
 [tagObjectId] [varchar] (900) NOT NULL -- Foreign Key to the Tag Object 
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TagObjectValues] ADD CONSTRAINT [PK_TagObjectValues] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_TagObjectValues_SystemInfoId] ON [dbo].[TagObjectValues] 
(
 [systemInfoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_TagObjectValues_TagObjectId] ON [dbo].[TagObjectValues] 
(
 [tagObjectId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[GroupPolicySysvolInfo] **/

CREATE TABLE [dbo].[GroupPolicySysvolInfo](
 [Id] [uniqueidentifier] NOT NULL, -- Foreign Key to the status check record for the DC that has the connection
 [DisplayName] [varchar] (255) NOT NULL,
 [DirectoryPath] [varchar] (255) NOT NULL, -- full path the directory where the group policy is located
 [CreatedUTC] [datetime2] NOT NULL,
 [ModifiedUTC] [datetime2] NOT NULL,
 [Version] [varchar] (255) NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[GroupPolicySysvolInfo] ADD CONSTRAINT [PK_GroupPolicySysvolInfo] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[TimeSyncInfo] **/

CREATE TABLE [dbo].[TimeSyncInfo](
 [Id] [uniqueidentifier] NOT NULL, -- Foreign Key to the SystemInfo record
 [LeapIndicator] [varchar] (255) NOT NULL,
 [Stratum] [varchar] (255) NOT NULL,
 [Precision] [varchar] (255) NOT NULL,
 [RootDelay] [varchar] (255) NOT NULL,
 [RootDispersion] [varchar] (255) NOT NULL,
 [PollInterval] [varchar] (255) NOT NULL,
 [PhaseOffset] [varchar] (255) NOT NULL,
 [ClockRate] [varchar] (255) NOT NULL,
 --
 [StateMachine] [varchar] (255) NOT NULL,
 [TimeSourceFlags] [varchar] (255) NOT NULL,
 [ServerRole] [varchar] (255)NOT NULL,
 [LastSyncError] [varchar] (255) NOT NULL,
 --
 [TimeSinceLastGoodSync] [varchar] (255) NOT NULL, -- TimeSpan.ToString()
 [LastSuccessfulSync] [datetime2] NOT NULL, -- local server time
 [Source] [varchar] (255) NOT NULL, -- source server name
 [SourceIPAddress] [varchar] (255) NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TimeSyncInfo] ADD CONSTRAINT [PK_TimeSyncInfo] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/**  Table [dbo].[NetstatInfo] **/

CREATE TABLE [dbo].[NetstatInfo](
 [Id] [bigint] IDENTITY(1,1) NOT NULL,
 [Protocol] [int] NOT NULL,
 [LocalAddress] [varchar] (255) NOT NULL,
 [LocalPort] [int] NOT NULL,
 [ForeignAddress] [varchar] (255) NOT NULL,
 [ForeignPort] [int] NOT NULL, 
 [State] [int] NOT NULL,
 [ProcessId] [bigint] NOT NULL,
 [processInfoRecordId] [bigint] NULL, -- Foreign Key to the ProcessInfo record 
 [systemInfoId] [uniqueidentifier] NOT NULL -- Foreign Key to the SystemInfo record 
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[NetstatInfo] ADD CONSTRAINT [PK_NetstatInfo] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_NetstatInfo_ProcessInfoRecordId] ON [dbo].[NetstatInfo] 
(
 [processInfoRecordId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_NetstatInfo_SystemInfoId] ON [dbo].[NetstatInfo] 
(
 [systemInfoId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Audit] ******/
CREATE TABLE [dbo].[Audit](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DateTimeUTC] [datetime] NOT NULL,
	[Username] [varchar](255) NOT NULL, -- domain\username
	[AuditEntry] [varbinary](max) NOT NULL -- compressed byte array.  Text is a list of entities added/modified/deleted, and the property values
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Audit] ADD CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Audit_DateTimeUTC] ON [dbo].[Audit]
(
	[DateTimeUTC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Audit_Username] ON [dbo].[Audit]
(
	[Username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/** Foreign Keys **/
USE [DCHealthCheck]
GO

ALTER TABLE [dbo].[SystemInfo]  WITH CHECK ADD  CONSTRAINT [FK_SystemInfo_HealthCheckSummary] FOREIGN KEY([HealthCheckSummaryId])
REFERENCES [dbo].[HealthCheckSummary] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[SystemInfo] CHECK CONSTRAINT [FK_SystemInfo_HealthCheckSummary]
GO

--

ALTER TABLE [dbo].[SystemInfo]  WITH CHECK ADD  CONSTRAINT [FK_SystemInfo_DomainController] FOREIGN KEY([domainControllerId])
REFERENCES [dbo].[DomainController] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[SystemInfo] CHECK CONSTRAINT [FK_SystemInfo_DomainController]
GO

--

ALTER TABLE [dbo].[Certificate]  WITH CHECK ADD  CONSTRAINT [FK_Certificate_SystemInfo] FOREIGN KEY([systemInfoId])
REFERENCES [dbo].[SystemInfo] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Certificate] CHECK CONSTRAINT [FK_Certificate_SystemInfo]
GO

--

ALTER TABLE [dbo].[ReplicationNeighbor]  WITH CHECK ADD  CONSTRAINT [FK_ReplicationNeighbor_SystemInfo] FOREIGN KEY([systemInfoId])
REFERENCES [dbo].[SystemInfo] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ReplicationNeighbor] CHECK CONSTRAINT [FK_ReplicationNeighbor_SystemInfo]
GO

--

ALTER TABLE [dbo].[ProcessInfo]  WITH CHECK ADD  CONSTRAINT [FK_ProcessInfo_SystemInfo] FOREIGN KEY([systemInfoId])
REFERENCES [dbo].[SystemInfo] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ProcessInfo] CHECK CONSTRAINT [FK_ProcessInfo_SystemInfo]
GO

 --
 
ALTER TABLE [dbo].[RoleFeature]  WITH CHECK ADD  CONSTRAINT [FK_RoleFeature_SystemInfo] FOREIGN KEY([SystemInfoId])
REFERENCES [dbo].[SystemInfo] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[RoleFeature] CHECK CONSTRAINT [FK_RoleFeature_SystemInfo]
GO

--

ALTER TABLE [dbo].[ServiceInfo]  WITH CHECK ADD  CONSTRAINT [FK_ServiceInfo_SystemInfo] FOREIGN KEY([systemInfoId])
REFERENCES [dbo].[SystemInfo] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ServiceInfo] CHECK CONSTRAINT [FK_ServiceInfo_SystemInfo]
GO

--

ALTER TABLE [dbo].[InstalledProduct]  WITH CHECK ADD  CONSTRAINT [FK_InstalledProduct_SystemInfo] FOREIGN KEY([systemInfoId])
REFERENCES [dbo].[SystemInfo] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[InstalledProduct] CHECK CONSTRAINT [FK_InstalledProduct_SystemInfo]
GO

--

ALTER TABLE [dbo].[PendingReplication]  WITH CHECK ADD  CONSTRAINT [FK_PendingReplication_SystemInfo] FOREIGN KEY([systemInfoId])
REFERENCES [dbo].[SystemInfo] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[PendingReplication] CHECK CONSTRAINT [FK_PendingReplication_SystemInfo]
GO

--

ALTER TABLE [dbo].[TagObjectValues]  WITH CHECK ADD  CONSTRAINT [FK_TagObjectValues_SystemInfo] FOREIGN KEY([systemInfoId])
REFERENCES [dbo].[SystemInfo] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[TagObjectValues] CHECK CONSTRAINT [FK_TagObjectValues_SystemInfo]
GO

--

ALTER TABLE [dbo].[TagObjectValues]  WITH CHECK ADD  CONSTRAINT [FK_TagObjectValues_TagObject] FOREIGN KEY([TagObjectId])
REFERENCES [dbo].[TagObject] ([DN])
GO

ALTER TABLE [dbo].[TagObjectValues] CHECK CONSTRAINT [FK_TagObjectValues_TagObject]
GO

--

ALTER TABLE [dbo].[GroupPolicySysvolInfo]  WITH CHECK ADD  CONSTRAINT [FK_GroupPolicySysvolInfo_SystemInfo] FOREIGN KEY([Id])
REFERENCES [dbo].[SystemInfo] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[GroupPolicySysvolInfo] CHECK CONSTRAINT [FK_GroupPolicySysvolInfo_SystemInfo]
GO

--

ALTER TABLE [dbo].[TimeSyncInfo]  WITH CHECK ADD  CONSTRAINT [FK_TimeSyncInfo_SystemInfo] FOREIGN KEY([Id])
REFERENCES [dbo].[SystemInfo] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[TimeSyncInfo] CHECK CONSTRAINT [FK_TimeSyncInfo_SystemInfo]
GO

--
 
ALTER TABLE [dbo].[NetstatInfo]  WITH CHECK ADD  CONSTRAINT [FK_NetstatInfo_ProcessInfo] FOREIGN KEY([processInfoRecordId])
REFERENCES [dbo].[ProcessInfo] ([Id])
--ON DELETE CASCADE
GO

ALTER TABLE [dbo].[NetstatInfo] CHECK CONSTRAINT [FK_NetstatInfo_ProcessInfo]
GO

--

ALTER TABLE [dbo].[NetstatInfo]  WITH CHECK ADD  CONSTRAINT [FK_NetstatInfo_SystemInfo] FOREIGN KEY([systemInfoId])
REFERENCES [dbo].[SystemInfo] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[NetstatInfo] CHECK CONSTRAINT [FK_NetstatInfo_SystemInfo]
GO
 
/** Permissions **/

USE [DCHealthCheck]
GO

CREATE USER [app-dchealthcheck] FOR LOGIN [app-dchealthcheck] WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember db_datareader, [app-dchealthcheck]
GO
EXEC sp_addrolemember db_datawriter , [app-dchealthcheck]  
GO
 
/** Views **/
USE [DCHealthCheck]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/****** Object:  View [dbo].[SystemInfoLatest]  ******/
/*
USE [DCHealthCheck]
GO
CREATE VIEW [dbo].[SystemInfoLatest]
AS
WITH CTE_DCInfo AS (SELECT Id, ADSite, DNSName, OSVersion FROM [DCHealthCheck].[dbo].[DomainController])
SELECT     dcsi.Id AS systemInfoId, CheckDateTimeUTC, CTE_DCInfo.DNSName, IPAddress, IPAddresses, 
			CTE_DCInfo.ADSite, Model, LogicalCPUCount AS CPUs, CTE_DCInfo.OSVersion, OSInstallDate, UTCOffset, DST, 
                      StartupDateTimeUTC, UptimeDays, DCTime, DCTimeUTC, TimeDiff, ProcessCount, FreeMemoryMB, 
					  TotalMemoryMB, TotalHandles, TotalThreads, 
					  CDriveTotalMB, CDriveFreeMB, TcpConnections, TotalLogons, DNSResponse, DNSListenAddresses, 
					  DNSPublishAddresses, DNSQueryReceived, 
					  DNSResponseSent, DNSRecursiveQueries, IsGlobalCatalog, IsPDCEmulator, IsInfrastructureManager, 
					  IsRIDManager, DCFlags, RepInMB, RepOutMB, 
					  HighestCommittedUSN, PendingReplicationCount, NTDSDitSizeMB, NeighborCount, 
					  PingTest, PingRttMs, PingLossPct, LdapBindTest, LdapSSLBindTest,
                      GCBindTest, GCSSLBindTest, DNSTest, SysvolTest, TimeServerTest, TimeSyncTest, 
					  StrictReplicationTest, ErrorMessage
FROM         [DCHealthCheck].[dbo].[SystemInfo] AS dcsi
INNER JOIN CTE_DCInfo AS CTE_DCInfo ON CTE_DCInfo.Id = dcsi.domainControllerId
WHERE     (dcsi.CheckDateTimeUTC IN
                          (SELECT     MAX(CheckDateTimeUTC) AS Expr1
                            FROM          [DCHealthCheck].[dbo].[SystemInfo]))
GO
							
/****** Object:  View [dbo].[ProcessInfoLatest]  ******/
/*
CREATE VIEW [dbo].[ProcessInfoLatest]
AS
WITH CTE_SystemInfo AS (SELECT     systemInfoId, DNSName
                                                          FROM          dbo.SystemInfoLatest AS dcsi)
    SELECT     CTE_SystemInfo.DNSName, dcpi.ProcessId, dcpi.ParentProcessId, dcpi.Priority, dcpi.StartDateTime, dcpi.ElapsedTime, dcpi.CPUTime, dcpi.CommandLine, 
                            dcpi.ExecutablePath, dcpi.PageFaults, dcpi.VirtualMemoryBytes, dcpi.WorkingSetBytes, dcpi.PrivateBytes, dcpi.PagePoolBytes, dcpi.NonPagePoolBytes, 
                            dcpi.HandleCount, dcpi.ThreadCount, dcpi.ReadBytes, dcpi.WriteBytes, dcpi.OtherBytes, dcpi.systemInfoRecordId
     FROM         dbo.ProcessInfo AS dcpi INNER JOIN
                            CTE_SystemInfo AS CTE_SystemInfo ON CTE_SystemInfo.systemInfoId = dcpi.systemInfoRecordId

GO

/****** Object:  View [dbo].[OscillatingConnectionsLatest]  ******/
/*
CREATE VIEW [dbo].[OscillatingConnectionsLatest]
AS
SELECT     dcsi.DNSName, dcrn.SourceServer, dcrn.SourceInvocationId, dcrn.ConsecutiveFailureCount, dcrn.LastAttemptedSyncUTC, dcrn.LastSuccessfulSyncUTC, 
                      dcrn.LastSyncMessage, dcrn.LastSyncResult, dcrn.PartitionName, dcrn.ReplicationNeighborOption, dcrn.ReplicationReason, dcrn.USNAttributeFilter, 
                      dcrn.USNLastObjectChangeSynced, dcrn.ErrorMessage, dcrn.systemInfoRecordId
FROM         dbo.SystemInfoLatest AS dcsi INNER JOIN
                      dbo.ReplicationNeighbor AS dcrn ON dcsi.systemInfoId = dcrn.systemInfoRecordId
WHERE     (dcrn.ReplicationReason LIKE '%OSCILLATING%')

GO

/****** Object:  View [dbo].[MissingSCOM2007Latest]  ******/
/*
CREATE VIEW [dbo].[MissingSCOM2007Latest]
AS
SELECT     *
FROM         dbo.SystemInfoLatest AS dcsil
WHERE     (DNSName NOT IN
                          (SELECT DISTINCT DNSName
                            FROM          dbo.ProcessInfoLatest
                            WHERE      (ExecutablePath LIKE '%healthservice%')))

GO

/****** Object:  View [dbo].[BESRelayLatest]  ******/
/*
CREATE VIEW [dbo].[BESRelayLatest]
AS

SELECT     *
FROM         dbo.SystemInfoLatest AS dcsil
WHERE     (DNSName IN
                          (SELECT DISTINCT DNSName
                            FROM          dbo.ProcessInfoLatest
                            WHERE      (ExecutablePath LIKE '%besrelay%')))
GO

/****** Object:  View [dbo].[TimeSyncInfoLatest] ******/
/*
CREATE VIEW [dbo].[TimeSyncInfoLatest]
AS
WITH CTE_SystemInfo AS (SELECT     systemInfoId, DNSName
                                                          FROM          dbo.SystemInfoLatest AS dcsi)
    SELECT     CTE_SystemInfo.DNSName, TimeSyncInfo.LeapIndicator, TimeSyncInfo.Stratum, TimeSyncInfo.Precision, TimeSyncInfo.RootDelay, TimeSyncInfo.RootDispersion, 
                            TimeSyncInfo.PollInterval, TimeSyncInfo.PhaseOffset, TimeSyncInfo.ClockRate, TimeSyncInfo.StateMachine, TimeSyncInfo.TimeSourceFlags, 
                            TimeSyncInfo.ServerRole, TimeSyncInfo.LastSyncError, TimeSyncInfo.TimeSinceLastGoodSync, TimeSyncInfo.LastSuccessfulSync, TimeSyncInfo.Source, 
                            TimeSyncInfo.SourceIPAddress
     FROM         dbo.TimeSyncInfo AS TimeSyncInfo INNER JOIN
                            CTE_SystemInfo AS CTE_SystemInfo ON CTE_SystemInfo.systemInfoId = TimeSyncInfo.Id

GO

/****** Object:  View [dbo].[TagObjectValuesLatest]   ******/
/*
CREATE VIEW [dbo].[TagObjectValuesLatest]
AS
WITH CTE_SystemInfo AS (SELECT     Id, DNSName
                                                                 FROM         dbo.SystemInfoLatest AS dcsi)
    SELECT     CTE_SystemInfo.DNSName, dct.TagObjectId, dct.TagValue
     FROM         dbo.TagObjectValues AS dct INNER JOIN
                            CTE_SystemInfo AS CTE_SystemInfo ON CTE_SystemInfo.Id = dct.ParentServerRecordId

GO


/****** Object:  View [dbo].[DCServiceInfoLatest]   ******/
/*
CREATE VIEW [dbo].[DCServiceInfoLatest]
AS
WITH CTE_SystemInfo AS (SELECT     systemInfoId, DNSName
                                                          FROM          dbo.SystemInfoLatest AS dcsi)
    SELECT     CTE_SystemInfo.DNSName, dcsi.DisplayName, dcsi.ErrorControl, dcsi.ExitCode, dcsi.Name, dcsi.PathName, dcsi.ProcessId, dcsi.ServiceType, dcsi.Started, 
                            dcsi.StartMode, dcsi.StartName, dcsi.State
     FROM         dbo.ServiceInfo AS dcsi INNER JOIN
                            dbo.SystemInfoLatest AS CTE_SystemInfo ON CTE_SystemInfo.systemInfoId = dcsi.systemInfoRecordId

GO
/*